package com.shdaily.android;

import java.io.FileNotFoundException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.test.mock.MockContext;

public class EngineDB {

	private static final String CREATE_TABLE_ARTICLES = "create table if not exists articles ("
		+ "id integer primary key autoincrement, "
		+ "title text not null, "
		+ "summary text not null, "
		+ "timeAgo text not null, "
		+ "categoryName text not null, "
		+ "author text not null, "
		+ "body text not null,"
		+ "hasPic integer not null, "
		+ "hasHD integer not null"
		+ ");";
	
	private static final String CREATE_TABLE_FAVORITES = "create table if not exists favorites ("
		+ "id integer primary key autoincrement, "
		+ "title text not null, "
		+ "summary text not null, "
		+ "timeAgo text not null, "
		+ "categoryName text not null, "
		+ "author text not null, "
		+ "body text not null,"
		+ "hasPic integer not null, "
		+ "hasHD integer not null"
		+ ");";

	private static final String CREATE_TABLE_TOPNEWS = "create table if not exists topnews ("
		+ "id integer primary key autoincrement, "
		+ "title text not null, "
		+ "summary text not null, "
		+ "timeAgo text not null, "
		+ "categoryName text not null, "
		+ "author text not null, "
		+ "body text not null,"
		+ "hasPic integer not null, "
		+ "hasHD integer not null"
		+ ");";

	private static final String CREATE_TABLE_IMAGES = "create table if not exists images ("
		+ "id integer primary key autoincrement, "
		+ "url text not null, "
		+ "author text not null, "
		+ "caption text not null, "
		+ "size text not null, "
		+ "article_id int not null"
		+ "foreign key(article_id) references article(id)"
		+ ");";
	
	private static final String CREATE_TABLE_TOPNEWS_IMAGES = "create table if not exists topnewsimages ("
		+ "id integer primary key autoincrement, "
		+ "url text not null, "
		+ "author text not null, "
		+ "caption text not null, "
		+ "size text not null, "
		+ "topnews_id int not null"
		+ "foreign key(topnews_id) references topnews(id)"
		+ ");";
	
	private static final String CREATE_TABLE_FAVORITES_IMAGES = "create table if not exists favoritesimages ("
		+ "id integer primary key autoincrement, "
		+ "url text not null, "
		+ "author text not null, "
		+ "caption text not null, "
		+ "size text not null, "
		+ "topnews_id int not null"
		+ "foreign key(favorite_id) references favorites(id)"
		+ ");";
	
	private static final String SELECT_TOPNEWS = "select * from topnews";
	private static final String SELECT_ARTICLE = "select * from articles where categoryName=";
	private static final String SELECT_FAVORITES = "select * from favorites";
	private static final String SELECT_TOPNEWS_IMAGES = "select * from topnewsimages where topnews_id=";
	private static final String SELECT_IMAGES = "select * from images where article_id=";
	private static final String SELECT_FAVORITES_IMAGES = "select * from favoritesimages where favorite_id=";
	
	private static final String DELETE_TOPNEWS = "delete * from topnews";
	private static final String DELETE_ARTICLES = "delete * from articles where categoryName=";
	private static final String DELETE_TOPNEWS_IMAGES = "delete * from topnewsimages";
	private static final String DELETE_IMAGES = "delete * from images";
	
	private static final String TOPNEWS_TABLE = "topnews";
	private static final String ARTICLES_TABLE = "articles";
	private static final String FAVORITES_TABLE = "favorites";
	private static final String DATABASE_NAME = "shdaily_db";
	private static final int DATABASE_VERSION = 1;
	
	private SQLiteDatabase db;

	public EngineDB(Context ctx) {
		try {
			db = ctx.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
		
				db.execSQL(CREATE_TABLE_TOPNEWS);
				db.execSQL(CREATE_TABLE_ARTICLES);
				db.execSQL(CREATE_TABLE_FAVORITES);
				db.execSQL(CREATE_TABLE_TOPNEWS_IMAGES);
				db.execSQL(CREATE_TABLE_IMAGES);
				db.execSQL(CREATE_TABLE_FAVORITES_IMAGES);
			} catch (Exception e1) {
				e1.printStackTrace();
				db = null;
			}
		
	}
	
	public ArrayList<Article> getTopNewsList() {
		ArrayList<Article> topNewsList = new ArrayList<Article>();
		Article currentArticle = new Article();
		Cursor cur = db.rawQuery(SELECT_TOPNEWS, new String [] {});
		for(cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {     
			// The Cursor is now set to the right position  
			int hasPic = 0;
			boolean hasHD = false;
			hasPic = cur.getInt(6);
			if (cur.getInt(7)==1)
				hasHD = true;
			currentArticle = new Article(cur.getInt(0), cur.getString(1), cur.getString(2), 
					cur.getString(3), cur.getString(4), cur.getString(5), hasPic, hasHD);
			topNewsList.add(currentArticle);
		}
		for(cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {     
			// The Cursor is now set to the right position  
			int hasPic = 0;
			boolean hasHD = false;
			hasPic = cur.getInt(6);
			if (cur.getInt(7)==1)
				hasHD = true;
			currentArticle = new Article(cur.getInt(0), cur.getString(1), cur.getString(2), 
					cur.getString(3), cur.getString(4), cur.getString(5), hasPic, hasHD);
			topNewsList.add(currentArticle);
		}
		for(int i=0; i<topNewsList.size(); i++) {     

			if (topNewsList.get(i).getHasPic()>0)
			{
				ArrayList<Image> images = new ArrayList<Image>();
				Image currentImage = new Image();
				Cursor imageCur = db.rawQuery(SELECT_TOPNEWS_IMAGES + topNewsList.get(i).getId(), new String [] {});
				for(imageCur.moveToFirst(); !imageCur.isAfterLast(); imageCur.moveToNext()) {     
					currentImage = new Image(cur.getInt(0), cur.getString(1), cur.getString(2), 
							cur.getString(3), cur.getString(4));
					images.add(currentImage);
				}
				topNewsList.get(i).setImageList(images);
			}
		}
		return topNewsList;
	}
	
	public ArrayList<Article> getArticleList(String categoryName) {
		ArrayList<Article> articleList = new ArrayList<Article>();
		Article currentArticle = new Article();
		Cursor cur = db.rawQuery(SELECT_ARTICLE + "'" + categoryName + "'", new String [] {});
		for(cur.moveToFirst(); cur.moveToNext(); cur.isAfterLast()) {     
			// The Cursor is now set to the right position  
			int hasPic = 0;
			boolean hasHD = false;
			hasPic = cur.getInt(6);
			if (cur.getInt(7)==1)
				hasHD = true;
			currentArticle = new Article(cur.getInt(0), cur.getString(1), cur.getString(2), 
					cur.getString(3), cur.getString(4), cur.getString(5), hasPic, hasHD);
			articleList.add(currentArticle);
		}
		for(cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {     
			// The Cursor is now set to the right position  
			int hasPic = 0;
			boolean hasHD = false;
			hasPic = cur.getInt(6);
			if (cur.getInt(7)==1)
				hasHD = true;
			currentArticle = new Article(cur.getInt(0), cur.getString(1), cur.getString(2), 
					cur.getString(3), cur.getString(4), cur.getString(5), hasPic, hasHD);
			articleList.add(currentArticle);
		}
		for(int i=0; i<articleList.size(); i++) {     

			if (articleList.get(i).getHasPic()>0)
			{
				ArrayList<Image> images = new ArrayList<Image>();
				Image currentImage = new Image();
				Cursor imageCur = db.rawQuery(SELECT_IMAGES + articleList.get(i).getId(), new String [] {});
				for(imageCur.moveToFirst(); !imageCur.isAfterLast(); imageCur.moveToNext()) {     
					currentImage = new Image(cur.getInt(0), cur.getString(1), cur.getString(2), 
							cur.getString(3), cur.getString(4));
					images.add(currentImage);
				}
				articleList.get(i).setImageList(images);
			}
		}
		return articleList;
	}
	
	public void refreshTopNewsList(ArrayList<Article> topNewsList)
	{
		db.rawQuery(DELETE_TOPNEWS, new String [] {});
		for (int i=0; i<topNewsList.size(); i++) { 
			int hasHD = 0;
			int hasPic = topNewsList.get(i).getHasPic();
			if (topNewsList.get(i).getHasHD()==true)
				hasHD = 1;
			db.rawQuery("insert into topnews values(" + topNewsList.get(i).getId()+ ", "
					+ "'" + topNewsList.get(i).getTitle() + "', "
					+ "'" + topNewsList.get(i).getSummary() + "', "
					+ "'" + topNewsList.get(i).getTimeAgo() + "', "
					+ "'" + topNewsList.get(i).getCategoryName() + "', "
					+ "'" + topNewsList.get(i).getAuthor() + "', "
					+ hasPic + ", "
					+ hasHD + ")", new String [] {});
			if (topNewsList.get(i).getImageList()!= null && !(topNewsList.get(i).getImageList().isEmpty()))
			{
				for (int j=0; j<topNewsList.get(i).getImageList().size(); j++)
				{
					db.rawQuery("insert into topnewsimages values(" + topNewsList.get(i).getImageList().get(j).getId()+ ", "
							+ "'" + topNewsList.get(i).getImageList().get(j).getURL() + "', "
							+ "'" + topNewsList.get(i).getImageList().get(j).getAuthor() + "', "
							+ "'" + topNewsList.get(i).getImageList().get(j).getCaption() + "', "
							+ "'" + topNewsList.get(i).getImageList().get(j).getSize() + "', "
							+ topNewsList.get(i).getId() + ")", new String[] {});
				}
			}
			
		}
	}
	
	public void refreshArticleList(ArrayList<Article> articleList, String categoryName)
	{
		db.rawQuery(DELETE_ARTICLES + "'" + categoryName + "'", new String [] {});
		for (int i=0; i<articleList.size(); i++) { 
			int hasHD = 0;
			int hasPic = articleList.get(i).getHasPic();
			if (articleList.get(i).getHasHD()==true)
				hasHD = 1;
			db.rawQuery("insert into articles values(" + articleList.get(i).getId()+ ", "
					+ "'" + articleList.get(i).getTitle() + "', "
					+ "'" + articleList.get(i).getSummary() + "', "
					+ "'" + articleList.get(i).getTimeAgo() + "', "
					+ "'" + articleList.get(i).getCategoryName() + "', "
					+ "'" + articleList.get(i).getAuthor() + "', "
					+ hasPic + ", "
					+ hasHD + ")", new String [] {});
			if (articleList.get(i).getImageList()!= null && !(articleList.get(i).getImageList().isEmpty()))
			{
				for (int j=0; j<articleList.get(i).getImageList().size(); j++)
				{
					db.rawQuery("insert into images values(" + articleList.get(i).getImageList().get(j).getId()+ ", "
							+ "'" + articleList.get(i).getImageList().get(j).getURL() + "', "
							+ "'" + articleList.get(i).getImageList().get(j).getAuthor() + "', "
							+ "'" + articleList.get(i).getImageList().get(j).getCaption() + "', "
							+ "'" + articleList.get(i).getImageList().get(j).getSize() + "', "
							+ articleList.get(i).getId() + ")", new String[] {});
				}
			}
			
		}
	}
	
	public void addToFavorites(Article art) {
		int hasHD = 0;
		int hasPic = art.getHasPic();
		if (art.getHasHD()==true)
			hasHD = 1;
		db.rawQuery("insert into favorites values(" + art.getId()+ ", "
				+ "'" + art.getTitle() + "', "
				+ "'" + art.getSummary() + "', "
				+ "'" + art.getTimeAgo() + "', "
				+ "'" + art.getCategoryName() + "', "
				+ "'" + art.getAuthor() + "', "
				+ hasPic + ", "
				+ hasHD + ")", new String [] {});
		if (art.getImageList()!= null && !(art.getImageList().isEmpty()))
		{
			for (int j=0; j<art.getImageList().size(); j++)
			{
				db.rawQuery("insert into favoritesimages values(" + art.getImageList().get(j).getId()+ ", "
						+ "'" + art.getImageList().get(j).getURL() + "', "
						+ "'" + art.getImageList().get(j).getAuthor() + "', "
						+ "'" + art.getImageList().get(j).getCaption() + "', "
						+ "'" + art.getImageList().get(j).getSize() + "', "
						+ art.getId() + ")", new String[] {});
			}
		}
	}
	
	public static void main(String[] args)
	{
		EngineDB db = new EngineDB(new MockContext());
		ArrayList<Article> topNewsList = new ArrayList<Article>();
		ArrayList<Image> imageList = new ArrayList<Image>();
		Article a1 = new Article(482698, 
				"Oh baby, you're one in ... 7 billion!", 
				"A nurse massages a new-born baby at a care center for premature and ill infants in a Shanghai maternity hospital yesterday. According to the United Nations website, the world's population is projected to pass 7 billion on October 31. The UN also predicted that the figure would reach 9.3 billion by 2050 and 10.1 billion by the end of this century. Countries with high birth rates, mainly in Africa, will see their populations tripled by then. Average life expectancy will be 81 by 2100.", 
				"", 
				3,
				false,
				"2011-09-16 01:48:03",
				"15 hr ago",
				"",
				imageList, 
				"A nurse massages a new-born baby at a care center for premature and ill infants in a Shanghai maternity hospital yesterday. According to the United Nations website, the world's population is projected to pass 7 billion on October 31. The UN also predicted that the figure would reach 9.3 billion by 2050 and 10.1 billion by the end of this century. Countries with high birth rates, mainly in Africa, will see their populations tripled by then. Average life expectancy will be 81 by 2100."
				);
		
		db.refreshTopNewsList(topNewsList);
	}
}
